﻿CREATE VIEW dbo.v_PurchaseContract_10
AS
SELECT     LI.LoanID, ISNULL(CC.Advance, CC_A.Advance) AS Advance, LI.DownPayment, CC.DownPayment_Min, CONVERT(numeric(18, 1), 
                      LI.DownPayment / NULLIF (LI.SalesPrice, 0) * 100) AS DownPaymentRatio, LI.AmountFinanced, dbo.MinNum(CC.AF_Max, 
                      dbo.SumNum5(LI.AmountFinanced, - LI.ServiceContract, - LI.GAP, 0, 0)) AS AF_Adjusted, CC.AFRatio_Max, 
                      LI.BookValue * CC.AFRatio_Max / 100 AS AF_MaxByBookValue, CC.SellPriceRatio_Max, 
                      LI.BookValue * CC.SellPriceRatio_Max / 100 AS SellPrice_MaxByBookValue, LI.LoanTerm, CC.Term_Max, LI.APR, CC_APR.APR AS APR_Min, 
                      LI.Payment, LI.PaymentOverride, LI.NumberOfPayments, LI.TotalPayments, LI.SalesPrice, LI.ModelAge, LI.BookValue, LI.BookValueDesc, 
                      LI.MilesPerYear, CC.MileagePerYear_Max, CC.Miles_Max, CC.OverageFeeRatio, CC.OverageAllowed, CC.FeeRate, CC.Fee_Max, 
                      CC_PTI.PTI AS PTI_Max, CONVERT(int, ROUND(LI.PaymentPerMonth / NULLIF (CI.GrossIncome, 0), 2) * 100.0) AS PTI, CC.DTI_Max, CONVERT(int, 
                      ROUND((CI.DebtNoCar + LI.Payment) / NULLIF (CI.GrossIncome, 0), 2) * 100.0) AS DTI, ISNULL(L_CC.OtherFeeName, N'Other Fee') AS OtherFeeName, 
                      L_CC.OtherFeeValue, CC.RecourseMonths, L_CC.AddMosRecourse, CC.DeductFeeFromReserve, CI.GrossIncome, L_NEI.NonEmploymentIncome, 
                      CI.DebtNoCar, CI.HousePayment, CI.Installment, CI.Revolving, CC.JobTime_Min, CC.ResidenceTime_Min, CC.AreaTime_Min, CC.ModelAge_Max, 
                      L_AT.MonthsCurrent, L_AT.MonthsInArea, L_AT.CurrPrevDistance, E.TimeLength AS JobTimeCurrent, CI.OpenUnitsAdjusted, 
                      ISNULL(SIGN(0 - CI.OpenUnitsAdjusted), - 1) AS OpenUnitsOK, LI.StatusID, CH.Nick AS ContractHolder, CH.ID AS ContractHolderID, CH.ContactName, 
                      CH.ContactPhone, CH.ContactEmail, CH.ContactFax, CH.Street AS ContractHolderStreet, CH.City AS ContractHolderCity, CH.State AS ContractHolderState,
                       CH.Zip AS ContractHolderZip, dbo.ts_State.StateName AS ContractHolderStateName, CH.AgreementDate AS AgreementDay, LI.IsSUV, LI.TrackerTypeID,
                       CC.TrackerReward, CONVERT(numeric(9, 2), LI.AmountFinanced - CC.AF_Max) * 0 AS Overage_AF_Max, CC.AF_Max, CC.ServiceContract_Max, 
                      LI.ServiceContract, CONVERT(int, ISNULL(SIGN(CC.ServiceContract_Max - LI.ServiceContract), 0)) AS ServiceContractOK, dbo.t_Borrower.FirstName, 
                      dbo.t_Borrower.LastName, LI.PurchaseBatchID, LI.DealerID, LI.Mileage, LI.GAP, LI.IsIndependent, LI.AddOnRateByPayment, LI.Lamp_SalesTaxAmount,
                       LI.ContractDate_F, LI.NonTaxableFees, LI.PaymentsPerYear, LI.PLTV, LI.PaymentPerMonth
FROM         dbo.t_ContractCondition AS CC INNER JOIN
                      dbo.v_ContractHolder AS CH ON CC.Code = CH.ConditionsCode INNER JOIN
                      dbo.v_TruthInLending AS LI ON CH.ID = LI.ContractHolderID INNER JOIN
                      dbo.t_Dealer AS D ON LI.DealerID = D.ID INNER JOIN
                      dbo.v_CreditInfo AS CI ON LI.LoanID = CI.LoanID INNER JOIN
                      dbo.v_Loan_AddressTime AS L_AT ON LI.LoanID = L_AT.LoanID INNER JOIN
                      dbo.v_Loan_NonEmploymentIncome AS L_NEI ON LI.LoanID = L_NEI.LoanID INNER JOIN
                      dbo.t_Loan_Borrower AS LB ON LI.LoanID = LB.LoanID INNER JOIN
                      dbo.t_Employment AS E ON LB.BorrowerID = E.BorrowerID INNER JOIN
                      dbo.t_Borrower ON LB.BorrowerID = dbo.t_Borrower.ID LEFT OUTER JOIN
                      dbo.ts_State ON CH.State = dbo.ts_State.StateCode LEFT OUTER JOIN
                      dbo.t_ContractCondition_PTIByGrossIncome AS CC_PTI ON CC.Code = CC_PTI.ContractConditionCode AND CI.GrossIncome BETWEEN 
                      CC_PTI.GrossIncomeFrom AND CC_PTI.GrossIncomeTo LEFT OUTER JOIN
                      dbo.t_ContractCondition_APRByModelAge AS CC_APR ON CC.Code = CC_APR.ContractConditionCode AND LI.ModelAge BETWEEN 
                      CC_APR.ModelAgeFrom AND CC_APR.ModelAgeTo LEFT OUTER JOIN
                      dbo.t_Loan_ContractConditions AS L_CC ON LI.LoanID = L_CC.LoanID LEFT OUTER JOIN
                      dbo.t_ContractCondition_AdvanceByDealerType AS CC_A ON LI.IsIndependent = CC_A.IsIndependent AND 
                      CC.Code = CC_A.ContractConditionCode
WHERE     (E.Pos = 0) AND (E.JobNumber = 0) AND (LB.Pos = 0)



GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[49] 4[31] 2[3] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1[50] 4[25] 3) )"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[54] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4[60] 2) )"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4) )"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2) )"
      End
      ActivePaneConfig = 9
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "CC"
            Begin Extent = 
               Top = 0
               Left = 392
               Bottom = 340
               Right = 592
            End
            DisplayFlags = 280
            TopColumn = 9
         End
         Begin Table = "CH"
            Begin Extent = 
               Top = 298
               Left = 208
               Bottom = 538
               Right = 363
            End
            DisplayFlags = 280
            TopColumn = 3
         End
         Begin Table = "LI"
            Begin Extent = 
               Top = 361
               Left = 403
               Bottom = 623
               Right = 591
            End
            DisplayFlags = 280
            TopColumn = 65
         End
         Begin Table = "D"
            Begin Extent = 
               Top = 399
               Left = 902
               Bottom = 647
               Right = 1060
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CI"
            Begin Extent = 
               Top = 167
               Left = 614
               Bottom = 303
               Right = 885
            End
            DisplayFlags = 280
            TopColumn = 9
         End
         Begin Table = "L_AT"
            Begin Extent = 
               Top = 505
               Left = 722
               Bottom = 614
               Right = 878
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L_NEI"
            Begin Extent = 
               Top = 667
               Left = 817
               Bottom = 761
               Right = 1009
            End
            DisplayFlags = 280
            TopColumn', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_PurchaseContract_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'= 0
         End
         Begin Table = "LB"
            Begin Extent = 
               Top = 537
               Left = 212
               Bottom = 631
               Right = 363
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "E"
            Begin Extent = 
               Top = 632
               Left = 403
               Bottom = 729
               Right = 554
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Borrower"
            Begin Extent = 
               Top = 613
               Left = 32
               Bottom = 740
               Right = 181
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ts_State"
            Begin Extent = 
               Top = 479
               Left = 15
               Bottom = 573
               Right = 166
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CC_PTI"
            Begin Extent = 
               Top = 142
               Left = 118
               Bottom = 268
               Right = 310
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CC_APR"
            Begin Extent = 
               Top = 12
               Left = 110
               Bottom = 120
               Right = 303
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L_CC"
            Begin Extent = 
               Top = 318
               Left = 706
               Bottom = 431
               Right = 869
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CC_A"
            Begin Extent = 
               Top = 9
               Left = 689
               Bottom = 117
               Right = 881
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
      PaneHidden = 
   End
   Begin DataPane = 
      PaneHidden = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 71
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1560
         Width = 1605
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_PurchaseContract_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane3', @value = N'Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 7845
         Alias = 2370
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_PurchaseContract_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 3, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_PurchaseContract_10';

